package com.summit.exam.dbdao;

import com.summit.exam.MysqlConnection;
import com.summit.exam.entity.ScoreInfoBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ScoreInfoDao {
    public boolean insertScore(String stuId, int paperId, int score, String title) {
        Connection conn = MysqlConnection.getMysqlConnection().getCon();
        String sql = "insert into score(stu_id, paper_id, score,title) values(?, ?, ?, ?)";
        int rs = 0;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, stuId);
            ps.setInt(2, paperId);
            ps.setInt(3, score);
            ps.setString(4, title);
            rs = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(ps, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rs != 0;
    }

    /********************************************
     *
     * @param: stuId
     * 查询信息该学生的考试信息
     ********************************************/
    public ArrayList<ScoreInfoBean> selectInfo(String stuID) {
        ArrayList<ScoreInfoBean> arrayList = new ArrayList<ScoreInfoBean>();
        Connection conn = MysqlConnection.getMysqlConnection().getCon();
        PreparedStatement stm = null;
        ResultSet resultSet = null;

        String sql = "select * from score where stu_id = ?";

        System.out.println(sql);
        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1, stuID);
            resultSet = stm.executeQuery();

            while (resultSet.next()) {
                ScoreInfoBean info = new ScoreInfoBean();

                info.setStuName(resultSet.getString("stu_id"));
                info.setPaperId(resultSet.getInt("paper_id"));
                info.setTitle(resultSet.getString("title"));
                info.setScore(resultSet.getInt("score"));

                arrayList.add(info);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(resultSet, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return arrayList;
    }

    public ArrayList<ScoreInfoBean> selectInfo2() {
        ArrayList<ScoreInfoBean> arrayList = new ArrayList<ScoreInfoBean>();
        Connection conn = MysqlConnection.getMysqlConnection().getCon();
        PreparedStatement stm = null;
        ResultSet resultSet = null;

        String sql = "select * from score";
        try {
            stm = conn.prepareStatement(sql);
            resultSet = stm.executeQuery();

            while (resultSet.next()) {
                ScoreInfoBean info = new ScoreInfoBean();

                info.setStuName(resultSet.getString("stu_id"));
                info.setPaperId(resultSet.getInt("paper_id"));
                info.setTitle(resultSet.getString("title"));
                info.setScore(resultSet.getInt("score"));

                arrayList.add(info);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(resultSet, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return arrayList;
    }


    public ScoreInfoBean selectInfo3(String stuID) {
        Connection conn = MysqlConnection.getMysqlConnection().getCon();
        PreparedStatement stm = null;
        ResultSet resultSet = null;
        ScoreInfoBean info = new ScoreInfoBean();
        String sql = "select * from score where stu_id = ?";

        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1, stuID);
            resultSet = stm.executeQuery();

            while (resultSet.next()) {

                info.setStuName(resultSet.getString("stu_id"));
                info.setPaperId(resultSet.getInt("paper_id"));
                info.setTitle(resultSet.getString("title"));
                info.setScore(resultSet.getInt("score"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(resultSet, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return info;
    }
}
